﻿/*
USE master
GO
DROP DATABASE Project_QLNHAC_ONLINE
GO
*/

CREATE DATABASE Project_QLNHAC_ONLINE
GO
USE Project_QLNHAC_ONLINE
GO
---- tạo bảng ca sĩ
 CREATE TABLE CaSi
 (
 CaSiId int IDentity(1,1),
 TenCaSi nvarchar(50) not null,
 NgaySinhCaSi date,
 TieuSuCaSi nvarchar(Max),
 LoaiCaSi nvarchar(50),
 CONSTRAINT PK_CaSi PRIMARY KEY(CaSiId)
 )
 GO
 ----tạo bảng nhạc sĩ
 CREATE TABLE NhacSi
 (
 NhacSiId int IDentity(1,1),
 TenNhacSi nvarchar(50) not null,
 NgaySinhNhacSi date,
 TieuSuNhacSi nvarchar,
 CONSTRAINT PK_NhacSi PRIMARY KEY(NhacSiId)
 )
 GO
  ---- tạo bảng cấp độ
  CREATE TABLE CapDo
 (
 CapDoId int IDentity(1,1),
 TenCapDo nvarchar(50) not null,
 SoCapDo int not null,
 PhanTramPhi nvarchar(50) not null,
 CONSTRAINT PK_CapDo PRIMARY KEY(CapDoId)
 )
 GO
  ----tạo bảng user
 CREATE TABLE NguoiDung
 (
 NguoiDungId int IDentity(1,1),
 Mail nvarchar(50) ,
 TenNguoiDung nvarchar(50) ,
 Pass nvarchar(50) ,
 NgaySinhNguoiDung date,
 GioiTinh bit,
 DiaChiNguoiDung nvarchar (150),
 SDTNguoiDung nvarchar(15),
 TienHienCo int,
 CapDoId int,
 CONSTRAINT PK_NhuoiDung PRIMARY KEY(NguoiDungId),
 CONSTRAINT FK_CapDo_NguoiDung FOREIGN KEY (CapDoId) REFERENCES CapDo(CapDoId),
 )
 GO
  ---- tạo bảng nhạc
 CREATE TABLE Nhac
 (
 NhacId int IDentity(1,1),
 NguoiDungId int not null,
 TenNhac nvarchar(50) not null,
 Phi int,
 Loai nvarchar,
 CONSTRAINT PK_Nhac PRIMARY KEY(NhacId),
 CONSTRAINT FK_NguoiDung_Nhac FOREIGN KEY (NguoiDungId) REFERENCES NguoiDung(NguoiDungId),
 )
 GO
  ---- chi Tiết nhạc
 CREATE TABLE ChiTietNhac
 (
 NhacId int not null,
 CaSiId int not null,
 NhacSiId int not null,
 CONSTRAINT PK_ChiTietNhac PRIMARY KEY(NhacId,CaSiId,NhacSiId),
 CONSTRAINT FK_Nhac_ChiTietNhac FOREIGN KEY (NhacId) REFERENCES Nhac(NhacId),
 CONSTRAINT FK_CaSi_ChiTietNhac FOREIGN KEY (CaSiId) REFERENCES CaSi(CaSiId),
 CONSTRAINT FK_NhacSi_ChiTietNhac FOREIGN KEY (NhacSiId) REFERENCES NhacSi(NhacSiId)
 )
 GO
 ----tạo bảng playlist
 CREATE TABLE Playlist
 (
 PlaylistId int IDentity(1,1),
 NguoiDungId int not null,
 NgayTao datetime not null,
 TenPlaylist nvarchar (20) not null,
 LoaiPlayist int not null,
 SoLuongBaiHat int not null, --- tổng số lượng bài hát có trog Playlist dùng để giải quyết số thứ tự trong bảng chi tiết
 CONSTRAINT PK_Playlist PRIMARY KEY(PlaylistId),
 CONSTRAINT FK_NguoiDung_Playlist FOREIGN KEY (NguoiDungId) REFERENCES NguoiDung(NguoiDungId)
 )
 GO
 ----tao bảng chi tiết playlist
 CREATE TABLE ChiTietPlaylist
 (
 PlaylistId int not null,
 NhacId int not null,
 SoThuTu int not null,------ chú thích-----------
 CONSTRAINT PK_ChiTietPlaylist PRIMARY KEY(PlaylistId),
 CONSTRAINT FK_PlaylistId_ChiTietPlaylist FOREIGN KEY (PlaylistId) REFERENCES Playlist(PlaylistId),
 CONSTRAINT FK_NhacId_ChiTietPlaylist FOREIGN KEY (NhacId) REFERENCES Nhac(NhacId)
 )
 GO
 ----tạo bảng lời bài hát
  CREATE TABLE Loi
 (
 NhacId int not null,
 NguoiDungId int not null,
 LoiId int IDentity(1,1),
 NoiDung nvarchar(150) not null,
 ThoiGianUp datetime not null,
 CONSTRAINT PK_Loi PRIMARY KEY(LoiId),
 CONSTRAINT FK_Nhac_Loi FOREIGN KEY (NhacId) REFERENCES Nhac(NhacId),
 CONSTRAINT FK_NguoiDung_Loi FOREIGN KEY (NguoiDungId) REFERENCES NguoiDung(NguoiDungId)
 )
 GO
 ----tạo bảng thể loại
  CREATE TABLE TheLoai
 (
 TheLoaiId int IDentity(1,1),
 TenTheLoai nvarchar(50) not null,
 MieuTa nvarchar,
 CONSTRAINT PK_TheLoai PRIMARY KEY(TheLoaiId)
 )
 GO
 ----chi tiết thể loại
  CREATE TABLE ChiTietTheLoai
 (
 TheLoaiId int not null,
 NhacId int  not null,
 CONSTRAINT PK_ChiTietTheLoai PRIMARY KEY(TheLoaiId,NhacId),
 CONSTRAINT FK_TheLoai_ChiTietTheLoai FOREIGN KEY (TheLoaiId) REFERENCES TheLoai(TheLoaiId),
 CONSTRAINT FK_Nhac_ChiTietTheLoai FOREIGN KEY (NhacId) REFERENCES Nhac(NhacId)
 )
 GO
 ---- tạo bảng view theo ngày
 CREATE TABLE ViewTheoNgay
 (
 ViewId int IDentity(1,1),
 NhacId int,
 SoLuongView int not null,
 Ngay datetime not null,
 CONSTRAINT PK_View PRIMARY KEY(ViewId),
 CONSTRAINT FK_Nhac_ViewTheoNhay FOREIGN KEY (NhacId) REFERENCES Nhac(NhacId), 
 )
 GO
 ----tạo bảng chi tiết like
 CREATE TABLE ChiTietLike
 (
 NguoiDungId int not null,
 NhacId int not null,
 CONSTRAINT PK_ChiTietLike PRIMARY KEY(NguoiDungId,NhacId),
 CONSTRAINT FK_NguoiDung_ChiTietLikeNhay FOREIGN KEY (NguoiDungId) REFERENCES NguoiDung(NguoiDungId),
 CONSTRAINT FK_Nhac_ChiTietLikeNhay FOREIGN KEY (NhacId) REFERENCES Nhac(NhacId)
 )
 GO
 ----tạo bảng thẻ nạp
  CREATE TABLE The
 (
 TheId int IDentity(1,1),
 MaThe nvarchar(10) not null,
 SeriThe nvarchar (12) not null,
 MenhGia int not null,
 CONSTRAINT PK_The PRIMARY KEY(TheId)
 )
 GO
 ----tạo bảng gói cước
  CREATE TABLE GoiCuoc
 (
 GoiCuocId int IDentity(1,1),
 SoTien nvarchar(10) not null,
 TenGoiCuoc nvarchar (12) not null,
 ThoiHan int not null,
 CONSTRAINT PK_GoiCuoc PRIMARY KEY(GoiCuocId)
 )
 GO
 ----tạo bảng tin tức
  CREATE TABLE TinTuc
 (
 TinTucId int IDentity(1,1),
 NguoiDungId int not null,
 ThoiGianUp datetime not null,
 TieuDe nvarchar(50) not null,
 NoiDung nvarchar (Max) not null,
 LuocXem int,
 CONSTRAINT PK_TinTuc PRIMARY KEY(TinTucId),
 CONSTRAINT FK_NguoiDung_TinTuc FOREIGN KEY (NguoiDungId) REFERENCES NguoiDung(NguoiDungId)
 )
 GO
 ----tạo bảng bình luận
  CREATE TABLE BinhLuan
 (
 BinhLuanId int IDentity(1,1),
 NhacId int not null,
 NguoiDungId int not null,
 LoiBinhTruocId int,
 LoiBinh nvarchar(Max),
 CONSTRAINT PK_BinhLuan PRIMARY KEY(BinhLuanId),
 CONSTRAINT FK_Nhac_BinhLuan FOREIGN KEY (NhacId) REFERENCES Nhac(NhacId),
 CONSTRAINT FK_NguoiDung_BinhLuan FOREIGN KEY (NguoiDungId) REFERENCES NguoiDung(NguoiDungId),
 CONSTRAINT FK_BinhLuanTruoc_BinhLuan FOREIGN KEY (LoiBinhTruocId) REFERENCES BinhLuan(BinhLuanId)
 )
 GO
 --- update dử liệu cho bảng ca sỉ
 /*
 INSERT INTO CaSi VALUES (N'nguyễn sơn khánh','1992/07/25','ldjfgljdlgjldjgjlkgfjlkj',N'Ngiệp dư')
 INSERT INTO CaSi VALUES (N'Đoàn Minh Hiếu','1992/07/25','ldjfgljdlgjldjgjlkgfjlkj',N'Ngiệp dư')
 INSERT INTO CaSi VALUES (N'Phạm Tuấn Thanh','1992/07/25','ldjfgljdlgjldjgjlkgfjlkj',N'Ngiệp dư')
 INSERT INTO CaSi VALUES (N'Võ Tấn Toàn','1992/07/25','ldjfgljdlgjldjgjlkgfjlkj',N'Ngiệp dư')
 
 ------ update dử liệu cho bảng ca sỉ
 INSERT INTO NguoiDung VALUES ('level4e_style@yahoo.com','nguyễn sơn khánh','123','1992/07/25',1,'dfdfdfdfd','011231231',1,1)
 */